﻿CREATE VIEW [dbo].[DIM_837I_835_Correlation]
AS 
	select BTSMessageID, BTSInterchangeID, 'TransactionStatus' = 
     CASE
          WHEN (select count(*) from dbo.DimCLM_835 b835 where b837I.Claim_CLM01_PatientAccountNumber = b835.ClaimPayment_CLP01_PatientAccountNumber) >= 1 THEN 'Response Received'
		  ELSE 'Incomplete'
     END,
'TransactionStatusDetails' = 
     CASE
          WHEN (select (ClaimPayment_CLP03_TotalClaimChargeAmount - ClaimPayment_CLP04_ClaimPaymentAmount) as 'PaymentDifferntial' from dbo.DimCLM_835 b835 where b837I.Claim_CLM01_PatientAccountNumber = b835.ClaimPayment_CLP01_PatientAccountNumber) <= 0 THEN 'Completed Payment Received'
          WHEN (select (ClaimPayment_CLP03_TotalClaimChargeAmount - ClaimPayment_CLP04_ClaimPaymentAmount) as 'PaymentDifferntial' from dbo.DimCLM_835 b835 where b837I.Claim_CLM01_PatientAccountNumber = b835.ClaimPayment_CLP01_PatientAccountNumber) > 0 THEN 'Partial Payment Received'
		  ELSE 'No detail information present'
     END, 
(Select top 1 BTSMessageID from dbo.DimCLM_835 b835 where b837I.Claim_CLM01_PatientAccountNumber = b835.ClaimPayment_CLP01_PatientAccountNumber) 
	as 'BTSMessageID_835', 
(Select top 1 BTSInterchangeID from dbo.DimCLM_835 b835 where b837I.Claim_CLM01_PatientAccountNumber = b835.ClaimPayment_CLP01_PatientAccountNumber) 
	as 'BTSInterchangeID_835',
 (SELECT     TOP (1) Receive_LocalTime
                            FROM          dbo.DimCLM_835 AS b835
                            WHERE      (Claim_CLM01_PatientAccountNumber = b837I.Claim_CLM01_PatientAccountNumber) 
                            ORDER BY Receive_LocalTime) AS LastTransactionDate

from DimCLM_837I b837I
;
--Drop VIEW [dbo].[BAM_837I_835]


